package com.uottawa.library.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.uottawa.library.domain.Loan;
import com.uottawa.library.domain.LoanPeriod;
import com.uottawa.library.domain.User;

import java.util.ArrayList;
import java.util.List;

public class LoanPeriodDAO extends BaseDAO{

	public boolean addLoanPeriod(LoanPeriod loanPeriod) {
		
		 String query = "INSERT INTO loan_period (period_id, duration) " +
		 		"values(" + loanPeriod.getPeriodId() + ", "+loanPeriod.getDuration()+ ")";

		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		}
		return true;
	}
	public boolean addLoanPeriod(int duration) {
	
		String query = "INSERT INTO `team7library`.`loan_period`(`period_id`,`duration`)  SELECT max(period_id)+1,"+duration+ " FROM team7library.loan_period";

		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}
	
	public int getCountLoanPeriod()
	{
		int count=0;
		String query = "Select COUNT(*) AS count FROM LOAN_PERIOD"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
				count = rs.getInt("count");
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			count = -1;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		
		return count;
	}

	public LoanPeriod getLoanPeriod(int i) {
		LoanPeriod loanPeriod = null;
		String query = "Select duration FROM LOAN_PERIOD WHERE PERIOD_ID="+i; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
					loanPeriod = new LoanPeriod();
					loanPeriod.setPeriodId(i);
				loanPeriod.setDuration(rs.getInt("duration"));
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		
		return loanPeriod;
	}

	public boolean updateLoanPeriod(LoanPeriod loanPeriod) {
		String query ="UPDATE loan_period SET duration ='"+ loanPeriod.getDuration() +"' WHERE period_id ='"+ loanPeriod.getPeriodId()+"'";
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
	}
	
	public int getMaxLoanPeriodId()
	{
		int maxId = 0;
		String query = "Select MAX(PERIOD_ID) AS MAX FROM LOAN_PERIOD"; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{
				if(rs.next())
				{
				maxId =  rs.getInt("MAX");
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		
		return maxId;
		
	}

	public boolean deleteLoanPeriod(LoanPeriod loanPeriod) {
		String query ="DELETE FROM loan_period  WHERE period_id ='"+ loanPeriod.getPeriodId()+"'";
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return true;
		
	}
	public boolean deleteLoanPeriod(int loanPeriodId) {
		String query ="DELETE FROM loan_period  WHERE period_id ='"+ loanPeriodId+"'";
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.execute(query);
		} catch (SQLException e) {
			return false;
		}
		return true;
		
	}

	public List<LoanPeriod> getAllLoanPeriod() {
		List <LoanPeriod> loanPeriodList = new ArrayList<LoanPeriod>();
		
		String query = "Select * FROM LOAN_PERIOD "; 
		Connection connection = getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs !=null)
			{

				while(rs.next())
				{
					LoanPeriod loanPeriod = new LoanPeriod();
					loanPeriod.setPeriodId(rs.getInt("period_id"));
					loanPeriod.setDuration(rs.getInt("duration"));
					loanPeriodList.add(loanPeriod);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return loanPeriodList;
	}



}
